-- =============================================
-- Author:        Paul Rogers
-- Create date: August 2006
-- Description:    Obtain the campaign for a given
-- appeal and set the ResponseTypeCode for all
-- AppealParticipation records that are associated
-- with the campaign for the particular respondent.
-- =============================================

CREATE PROCEDURE [dbo].[asi_SetAppealParticipationResponseTypeCodes]
    @respondentKey uniqueidentifier,
    @appealKey uniqueidentifier,
    @responseTypeCode int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

BEGIN TRAN

    DECLARE @campaignKey UNIQUEIDENTIFIER
    DECLARE @returnCount int
    SET @returnCount = 0

    -- Given an Appeal, figure out the Campaign
    SELECT @campaignKey = cm.CampaignKey
    FROM CampaignMain cm
    INNER JOIN AppealMain am ON am.CampaignKey = cm.CampaignKey
    WHERE am.AppealKey=@appealKey

    -- Set the response type code for all
    -- AppealParticipation records involving
    -- the Campaign and the Respondent
    UPDATE AppealParticipation
    SET
        ResponseTypeCode=@responseTypeCode
    FROM AppealParticipation ap
    INNER JOIN AppealMain am ON ap.AppealKey = am.AppealKey
    INNER JOIN CampaignMain cm ON am.CampaignKey = cm.CampaignKey
    WHERE ap.RespondentUserKey=@respondentKey
    AND cm.CampaignKey = @campaignKey

    SET @returnCount = @@ROWCOUNT

    
    SELECT @returnCount AS ReturnCount
COMMIT TRAN
END

GO